﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MMK.PaketTracker.Server.Definitions;
using System.Data.SqlClient;
using System.Data;

namespace MMK.PaketTracker.Server.Database
{
    public class PaketStatusSqlDataLayer : BaseSqlDataLayer
    {
        public PaketStatusSqlDataLayer(string DbConnectionString) { this.Initialize(DbConnectionString); }    

        public void CreateNewPaketStatus(string paketIdent, PaketStatusType status, string statusBeschreibung, Guid mitarbeiterID, Guid stationID)
        {
            string sql = "INSERT INTO [PaketStatus] VALUES (@ID, @PaketIdent, @Zeitstempel, @Status, @StatusBeschreibung, @MitarbeiterID, @StationID)";

            this.ExecuteNonQuery(sql,
                new SqlParameter("@ID", Guid.NewGuid()),
                new SqlParameter("@PaketIdent", paketIdent),
                new SqlParameter("@Zeitstempel", DateTime.Now),
                new SqlParameter("@Status", status),
                new SqlParameter("@statusBeschreibung", this.CheckNull(statusBeschreibung)),
                new SqlParameter("@MitarbeiterID", mitarbeiterID),
                new SqlParameter("@StationID", stationID));
        }

        public PaketStatus GetLastPaketStatus(string paketIdent)
        {
            string sql = "SELECT TOP 1 * FROM PaketStatus WHERE PaketIdent=@PaketIdent ORDER BY Zeitstempel DESC";

            using (IDataReader reader = this.GetDataReader(sql, new SqlParameter("@PaketIdent", paketIdent)))
            {
                return (reader.Read() ? LoadPaketStatusFromDataReader(reader) : null);
            }
        }

        public List<PaketStatus> GetAllPaketStatus(string paketIdent)
        {
            List<PaketStatus> list = new List<PaketStatus>();
            
            string sql = "SELECT * FROM PaketStatus WHERE PaketIdent=@PaketIdent ORDER BY Zeitstempel DESC";

            using (IDataReader reader = this.GetDataReader(sql, new SqlParameter("@PaketIdent", paketIdent)))
            {
                while (reader.Read())
                {
                    list.Add(LoadPaketStatusFromDataReader(reader));
                }
            }

            return list;
        }

        public GPSCoordinate GetFahrzeugPosition(string paketIdent)
        {
            string sql = "SELECT TOP 1 * FROM [FahrzeugGPSData] a INNER JOIN [FahrzeugPaket] b ON a.FahrzeugID = b.FahrzeugID WHERE b.PaketIdent=@PaketIdent ORDER BY a.Zeitstempel DESC";

            using(IDataReader reader = this.GetDataReader(sql, new SqlParameter("@PaketIdent", paketIdent)))
            {
                while (reader.Read())
                {
                    GPSCoordinate c = new GPSCoordinate()
                    {
						Latitude = this.GetDouble(reader, "Latitude"),
						Longitude = this.GetDouble(reader, "Longitude"),
                        Zeitstempel = this.GetDateTime(reader, "Zeitstempel"),
                    };

                    return c;
                }
            }

            return null;
        }

        private PaketStatus LoadPaketStatusFromDataReader(IDataReader reader)
        {
            PaketStatus status = new PaketStatus();
            status.Status = (PaketStatusType)this.GetInt32(reader, "Status");
            status.Beschreibung = this.GetString(reader, "StatusBeschreibung");
            status.Coordinate = null;
            status.MitarbeiterID = this.GetGuid(reader, "MitarbeiterID");
            status.StationID = this.GetGuid(reader, "StationID");
            status.Zeitstempel = this.GetDateTime(reader, "Zeitstempel");

            return status;
        }
    }
}
